Archiving Audit Data |
Introduction
The auditing component writes information to tables in the database specified on the audit service container. Depending upon the auditing configuration, the amount of information stored in the audit tables can grow very quickly. Typically, some form of archiving of the stored audit information is required. To assist in the archiving of the audit information, database scripts that can archive the audit data are available. This page describes how to install and use the database scripts.
Target Audience
The target audience for this document are the database administrators. It is assumed that the reader has knowledge of the basic database administration tasks such as creating tables, stored procedures, and executing stored procedures.
Provided Functionality
The archiving scripts can be used to archive the contents of the tables CORDYS_ARTIFACTS_AUDIT and CORDYS_ARTIFACTS_REVISIONS. The first table contains the identifying information of an artifact for which an audit trail is stored; the second table contains the audit trail itself.
Archiving starts from the CORDYS_ARTIFACTS_REVISIONS table. All the records that match the archiving criteria are moved from the source table to the archive table 'ARCHIVE_ARTIFACTS_REVISIONS'. As a part of this, the corresponding parent records from CORDYS_ARTIFACTS_AUDIT are moved to the archive table ARCHIVE_ARTIFACTS_AUDIT, only if the same information does not exist in the archive table.
Refer to the Archiving Criteria section in this topic for more information on archiving criteria.
Installation
The archiving scripts are not automatically installed when installing Process Platform. The scripts are located in the directory <Process_Platform_Installation_Directory>/components/audit/dbschema/archivescripts. There are three scripts in this directory, one for each supported database type:
- audit_archive_mssql.sql for Microsoft SQL Server databases
- audit_archive_mysql.sql for MySQL databases
- audit_archive_oracle.sql for Oracle databases
The script must be run in the context of the database (schema) that contains the audit tables CORDYS_ARTIFACTS_AUDIT and CORDYS_ARTIFACTS_REVISIONS.
After the script is run, a number of stored procedures are created that together provide the archiving functionality.
Creating Archive Tables
The target tables for the archiving are automatically created when the scripts are run for the first time. These tables are created in the same schema where the audit tables are located. It is possible to create the tables manually. For example, if specific storage options are required.
The structure of the target tables are copies of the source tables with some columns added:
- CORDYS_ARTIFACTS_AUDIT is archived to ARCHIVE_ARTIFACTS_AUDIT; in the target table, one additional column 'ARCHIVE_DATE' is added. This column contains the date without time, on which the record was created or archived.
- CORDYS_ARTIFACTS_REVISIONS is archived to ARCHIVE_ARTIFACTS_REVISIONS; in the target table, two additional columns are added:
- ARCHIVE_DATE contains the date without time, on which the record was archived.
- AUDIT_DATE contains date with time, version of the date-time the original record was created, calculated from the value of the PERFORMED_AT column.
Note: On MySQL, this column does not contain the milliseconds.
For Microsoft SQL Server
Table ARCHIVE_ARTIFACTS_AUDIT:
SELECT TOP 0 * INTO [ARCHIVE_ARTIFACTS_AUDIT] FROM [CORDYS_ARTIFACTS_AUDIT] ALTER TABLE [ARCHIVE_ARTIFACTS_AUDIT] ADD [ARCHIVE_DATE] DATETIME CREATE UNIQUE INDEX [UX_AUDIT_ID] ON [ARCHIVE_ARTIFACTS_AUDIT] ([AUDIT_ID])
Table ARCHIVE_ARTIFACTS_REVISIONS:
SELECT TOP 0 * INTO [ARCHIVE_ARTIFACTS_REVISIONS] FROM [CORDYS_ARTIFACTS_REVISIONS] ALTER TABLE [ARCHIVE_ARTIFACTS_REVISIONS] ADD [ARCHIVE_DATE] DATETIME, [AUDIT_DATE] DATETIME
For MySQL
Table ARCHIVE_ARTIFACTS_AUDIT:
CREATE TABLE `ARCHIVE_ARTIFACTS_AUDIT` LIKE `CORDYS_ARTIFACTS_AUDIT`; ALTER TABLE `ARCHIVE_ARTIFACTS_AUDIT` ADD COLUMN ARCHIVE_DATE DATE; CREATE UNIQUE INDEX `UX_AUDIT_ID` ON `ARCHIVE_ARTIFACTS_AUDIT` (`AUDIT_ID`);
Table ARCHIVE_ARTIFACTS_REVISIONS:
CREATE TABLE `ARCHIVE_ARTIFACTS_REVISIONS` LIKE `CORDYS_ARTIFACTS_REVISIONS`; ALTER TABLE `ARCHIVE_ARTIFACTS_REVISIONS` ADD COLUMN ARCHIVE_DATE DATE, ADD COLUMN RECORD_DATE DATE;
For Oracle
Table ARCHIVE_ARTIFACTS_AUDIT:
CREATE TABLE ARCHIVE_ARTIFACTS_AUDIT AS SELECT * FROM CORDYS_ARTIFACTS_AUDIT WHERE ROWNUM < 1; ALTER TABLE ARCHIVE_ARTIFACTS_AUDIT ADD ARCHIVE_DATE DATE; CREATE UNIQUE INDEX UX_AUDIT_ID ON ARCHIVE_ARTIFACTS_AUDIT (AUDIT_ID);
Table ARCHIVE_ARTIFACTS_REVISIONS:
CREATE TABLE ARCHIVE_ARTIFACTS_REVISIONS AS SELECT * FROM CORDYS_ARTIFACTS_REVISIONS WHERE ROWNUM < 1; ALTER TABLE ARCHIVE_ARTIFACTS_REVISIONS ADD ARCHIVE_DATE DATE; ALTER TABLE ARCHIVE_ARTIFACTS_REVISIONS ADD RECORD_DATE DATE;
Usage
After the archiving scripts are installed, a stored procedure is created to archive the auditing data. This stored procedure accepts four parameters. Refer to the Archiving Criteria section below for an explanation of the archiving criteria:
- Archive date, a required parameter
- Batch size, a required parameter
- Organization, an optional parameter, default is NULL
- Artifact type, an optional parameter, default is NULL
 |
Optional parameters For Oracle and Microsoft SQL Server, the stored procedures support the actual optional parameters. This means that the parameter does not have to be specified in the call to the stored procedure, and the default value is used. MySQL does not support the optional parameters; therefore, while using the MySQL variant, NULL values have to be specified for the optional parameters when calling the stored procedure. |
The Batch size parameter specifies the number of records from CORDYS_ARTIFACTS_REVISIONS that will be archived in a single database transaction. A typical value for this parameter is between 10,000 and 100,000. Choosing a too low value impacts the performance of the archiving significantly while choosing a too high value may cause issues with too long running transactions and/or running out of space in the transaction logs depending upon the type of database.
Archiving Criteria
The archiving scripts always require an archival date as the input. All audit records older than this date are archived. Next to the archive date, there are two more optional criteria that can be used:
- Organization: This defines the organization for which the audit records are archived. The value of this parameter must be the
full LDAP DNof the organization. If this parameter is not specified, then all the audit records that match the other criteria are archived. If the string 'SHARED' is provided, then all the audit records for the shared level artifacts are archived. - Artifact type: This defines the artifact type for which the audit records are archived. If this artifact type is not specified, then all the audit records that match the other criteria are archived.
Examples of artifact types are:
- LDAP: Any changes made to the LDAP content, such as adding a role to a user, changing user information, or deleting a user.
- Authentication: Authentication related events, such as logins, when authentication is handled by Process Platform and not by an external Identity Provider.
Microsoft SQL Server
To archive all the data created before January 15 with a batch size of 10,000 records:
exec sp_archive_audit @in_archive_date = '2013-01-15', @in_batch_size = 10000;
To archive all the data for a specific organization that was created before January 15 with a batch size of 10,000 records:
exec sp_archive_audit @in_archive_date = '2013-01-15', @in_batch_size = 10000, @in_organization = '<LDAP DN of the organization>';
To archive all the data for the Authentication artifact type that was created before January 15 with a batch size of 10,000 records:
exec sp_archive_audit @in_archive_date = '2013-01-15', @in_batch_size = 10000, @in_artifact_type = 'Authentication';
To archive all the data for the Authentication artifact type and a specific organization that was created before January 15 with a batch size of 10,000 records:
exec sp_archive_audit @in_archive_date = '2013-01-15', @in_batch_size = 10000, @in_organization = '<LDAP DN of the organization>', @in_artifact_type = 'Authentication';
To archive all the data older than seven days with a batch size of 10,000 records:
DECLARE @archive_date DATETIME SET @archive_date = DATEADD(dd, -7, GETDATE()); exec sp_archive_audit @in_archive_date = @archive_date, @in_batch_size = 10000;
MySQL
To archive all the data created before January 15 with a batch size of 10,000 records:
call sp_archive_audit('2013-01-15', 10000, NULL, NULL);
To archive all the data for a specific organization that was created before January 15 with a batch size of 10,000 records:
call sp_archive_audit('2013-01-15', 10000, '<LDAP DN of the organization>', NULL);
To archive all the data for the Authentication artifact type that was created before January 15 with a batch size of 10,000 records:
call sp_archive_audit('2013-01-15', 10000, NULL, 'Authentication');
To archive all the data for the Authentication artifact type and a specific organization that was created before January 15 with a batch size of 10,000 records:
call sp_archive_audit('2013-01-15', 10000, '<LDAP DN of the organization>', 'Authentication');
To archive all the data older than seven days with a batch size of 10,000 records:
call sp_archive_audit(ADDDATE(CURDATE(), INTERVAL -7 DAY), 10000, NULL, NULL);
Oracle
|
PL/SQL Package The Oracle version of the archiving functionality are bundled into a single PL/SQL package called ARCHIVE_AUDIT. |
To archive all the data created before January 15 with a batch size of 10,000 records:
exec archive_audit.sp_archive_audit(TO_DATE('20130115', 'YYYYMMDD'), 10000);
To archive all the data for a specific organization that was created before January 15 with a batch size of 10,000 records:
exec archive_audit.sp_archive_audit(TO_DATE('20130115', 'YYYYMMDD'), 10000, '<LDAP DN of the organization>');
To archive all the data for the Authentication artifact type that was created before January 15 with a batch size of 10,000 records:
exec archive_audit.sp_archive_audit(TO_DATE('20130115', 'YYYYMMDD'), 10000, NULL, 'Authentication');
To archive all the data for the Authentication artifact type and a specific organization that was created before January 15 with a batch size of 10,000 records:
exec archive_audit.sp_archive_audit(TO_DATE('20130115', 'YYYYMMDD'), 10000, '<LDAP DN of the organization>', 'Authentication');
To archive all the data older than 7 days with a batch size of 10,000 records
exec archive_audit.sp_archive_audit(SYSDATE-7, 10000);
Known Limitations
Possible Conflict between Archiving and Auditing
As the archiving scripts remove records from the CORDYS_ARTIFACTS_REVISIONS, there is a possibility that a transaction in Process Platform is being audited for the same artifact instance that has just been deleted by the archiving script. In this case, the transaction that is audited can fail. It is advised to run the archiving script only for a date, which is a few days prior to the current date and either during a maintenance window or at a time when the number of transactions is the lowest.
More Records Processed than the Specified Batch Size
The actual number of records that are processed in a single transaction are usually higher than the specified batch size. This is caused by the fact that the batch size is used to determine the number of records from the CORDYS_ARTIFACTS_REVISIONS table that are processed. As part of the archiving related records from CORDYS_ARTIFACTS_AUDIT are also copied or moved, these do not count against the batch size.